{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 期中作业"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- 数据：2020年美国债券交易市场数据，截取了前5000条\n",
    "  - ..\\lecture-python\\raw-data\\bond_intraday_trade.csv\n",
    "- 使用的变量(variables)\n",
    "  - `'cusip_id'`: the unique ID of the bond\n",
    "  - `'trd_exctn_dt'`: the date of the bond trade\n",
    "  - `'trd_exctn_tm'`: the time of the bond trade\n",
    "  - `'rptd_pr'`: reported price\n",
    "- 目标：计算 Daily Roll’s Measure （衡量债券流动性的指标）\n",
    "- **问题**：\n",
    "  1. 请指出下列计算Roll's measure code 中的一个明显的bug (有重复出现)\n",
    "  2. 思考一个优化coding的思路并进行修改\n",
    
    "- **提交**\n",
    "  - 提交文件命名方式：`姓名-学号-roll-measure.ipynb`\n",
    "    - 如：贾东晓-19XXXX-roll-measure.ipynb\n",
    "  - 在gitee中使用pull request提交至 HW/HW-submit 文件夹\n",
    "  - 注意相对路径使用是以HW-submit文件夹为起点，建议建立分支同步课程仓库，在HW-submit文件夹中复制作业文件并修改。\n",
    "\n",
    "- 截止时间：2022年4月26日晚10点前"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Daily Roll’s Measure\n",
    "\n",
    "1. delete all records of which the cusip_id is missing\n",
    "2. sort the data by cusip_id, trade date\n",
    "3. Within each trade date, for each bond, calculate return \n",
    "   $$R_t = \\frac{P_t - P_{t-1}}{P_{t-1}}$$\n",
    "4. within each trade date, for each bond, the roll’s measure is\n",
    "   $$\\text{Roll's  measure} = 2\\sqrt{-cov(R_t,R_{t-1})}$$\n",
    "\n",
    "   - if daily cov > 0, there are 4 ways to deal with it:\n",
    "     - roll's measure = na\n",
    "     - roll's measure = 0\n",
    "     - take the square root without applying the negative sign and treat the result as a negative spread.\n",
    "     - treat positive covariances as if they are negative, resulting in a positive Roll spread estimate"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Calculate Daily Roll’s Measure"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### import data and quick check"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\IPython\\core\\interactiveshell.py:3444: DtypeWarning: Columns (10,22,29,31,32) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  exec(code_obj, self.user_global_ns, self.user_ns)\n"
     ]
    }
   ],
   "source": [
    "data = pd.read_csv('traceclean.csv')  #请自己修改路径"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# data.head()\n",
    "# using columns:\n",
    "    # cusip_id\n",
    "    # trd_exctn_dt\n",
    "    # trd_exctn_tm\n",
    "    # rptd_pr\n",
    "data_use = data[['cusip_id','trd_exctn_dt','trd_exctn_tm','rptd_pr']]\n",
    "data_use.columns=['id','date','hour','price']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>hour</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00037BAB8</td>\n",
       "      <td>20200313</td>\n",
       "      <td>11:14:41</td>\n",
       "      <td>100.351</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00037BAB8</td>\n",
       "      <td>20200331</td>\n",
       "      <td>13:04:45</td>\n",
       "      <td>101.019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>00037BAB8</td>\n",
       "      <td>20200407</td>\n",
       "      <td>10:07:24</td>\n",
       "      <td>101.285</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>00037BAB8</td>\n",
       "      <td>20200203</td>\n",
       "      <td>16:40:25</td>\n",
       "      <td>102.660</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>00037BAB8</td>\n",
       "      <td>20200203</td>\n",
       "      <td>16:40:25</td>\n",
       "      <td>102.910</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          id      date      hour    price\n",
       "0  00037BAB8  20200313  11:14:41  100.351\n",
       "1  00037BAB8  20200331  13:04:45  101.019\n",
       "2  00037BAB8  20200407  10:07:24  101.285\n",
       "3  00037BAB8  20200203  16:40:25  102.660\n",
       "4  00037BAB8  20200203  16:40:25  102.910"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "data_use.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 8856974 entries, 0 to 8856973\n",
      "Data columns (total 4 columns):\n",
      " #   Column  Dtype  \n",
      "---  ------  -----  \n",
      " 0   id      object \n",
      " 1   date    int64  \n",
      " 2   hour    object \n",
      " 3   price   float64\n",
      "dtypes: float64(1), int64(1), object(2)\n",
      "memory usage: 270.3+ MB\n",
      "--------------------\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "count    8.856974e+06\n",
       "mean     9.803020e+01\n",
       "std      3.296696e+01\n",
       "min      0.000000e+00\n",
       "25%      9.774000e+01\n",
       "50%      1.013000e+02\n",
       "75%      1.064570e+02\n",
       "max      1.000000e+04\n",
       "Name: price, dtype: float64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "data_use.info()\n",
    "print('-'*20)\n",
    "data_use['price'].describe()  # 这里使用的是部分数据，也许没有价格=0的数据\n",
    "# min price = 0, check？\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "id       1\n",
      "date     1\n",
      "hour     1\n",
      "price    1\n",
      "dtype: int64\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0.01</th>\n",
       "      <td>20200103.0</td>\n",
       "      <td>10.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0.02</th>\n",
       "      <td>20200107.0</td>\n",
       "      <td>10.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0.03</th>\n",
       "      <td>20200108.0</td>\n",
       "      <td>10.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0.04</th>\n",
       "      <td>20200109.0</td>\n",
       "      <td>19.302</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0.05</th>\n",
       "      <td>20200113.0</td>\n",
       "      <td>38.000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            date   price\n",
       "0.01  20200103.0  10.000\n",
       "0.02  20200107.0  10.000\n",
       "0.03  20200108.0  10.000\n",
       "0.04  20200109.0  19.302\n",
       "0.05  20200113.0  38.000"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# check price  = 0\n",
    "print(data_use[data_use['price']==0].count())\n",
    "# okay, one row:  drop this data\n",
    "# data_use[data_use['price']==0]\n",
    "# check quantile\n",
    "# data_use.quantile(q=[0.01,0.02,0.03,0.04,0.05])  # drop more data?\n",
    "\n",
    "# check if there are missing value in bond code\n",
    "# data_use['id'].isnull().any()\n",
    "# no missing value for bond id\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# first: drop price == 0\n",
    "data_use2 = data_use.drop(index=(data_use.loc[(data_use['price']==0)].index))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### calculate intra day return"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# first: adjust date format\n",
    "# join hour-minute-second,then change type to  int, just for sorting\n",
    "data_use2['hour_adj'] = data_use2['hour'].str.replace(':','').astype('int')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# adjust date format\n",
    "data_use2['date'] = pd.to_datetime(data_use2['date'],format='%Y%m%d')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# NOTE\n",
    "# 时间相同，价格不同？ 取均值处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# count some trading info\n",
    "low_trade_times = 0\n",
    "positive_cov = 0\n",
    "# calculate roll's measure\n",
    "def roll_liquidity1(df):\n",
    "    # if cov> 0, return np.nan\n",
    "    global low_trade_times, positive_cov\n",
    "    # first： 时间相同，取价格mean\n",
    "    temp = pd.DataFrame(df.groupby('hour_adj')['price'].mean()).sort_index(ascending=True)\n",
    "    # only calculate when size > 3\n",
    "    if temp.shape[0]>3:\n",
    "        rate = (df.price - df.price.shift(1))/df.price.shift(1)\n",
    "        c = np.cov(rate[1:-1],rate[2:])[0,1]  # bec 0 is na        \n",
    "        if c < 0:\n",
    "            return np.sqrt(-c)*2\n",
    "        else:\n",
    "            positive_cov += 1\n",
    "            return np.nan\n",
    "    low_trade_times += 1\n",
    "    return [np.nan]*4\n",
    "\n",
    "\n",
    "def roll_liquidity2(df):\n",
    "    # if cov> 0, return 0\n",
    "    #global low_trade_times, positive_cov\n",
    "    # first： 时间相同，取价格mean\n",
    "    temp = pd.DataFrame(df.groupby('hour_adj')['price'].mean()).sort_index(ascending=True)\n",
    "    # only calculate when size > 3\n",
    "    if temp.shape[0]>3:\n",
    "        rate = (df.price - df.price.shift(1))/df.price.shift(1)\n",
    "        c = np.cov(rate[1:-1],rate[2:])[0,1]  # bec 0 is na\n",
    "        if c < 0:\n",
    "            return np.sqrt(-c)*2\n",
    "        else:\n",
    "            #positive_cov += 1\n",
    "            return 0\n",
    "    #low_trade_times += 1\n",
    "    return np.nan   # 日内价格数量<3, 赋值np.nan, 以区分cov<0，事后可以方便改为0若有需要\n",
    "\n",
    "\n",
    "\n",
    "def roll_liquidity3(df):\n",
    "    # if cov> 0, take the square root without applying the negative sign and treat the result as a negative spread.\n",
    "    #global low_trade_times, positive_cov\n",
    "    # first： 时间相同，取价格mean\n",
    "    temp = pd.DataFrame(df.groupby('hour_adj')['price'].mean()).sort_index(ascending=True)\n",
    "    # only calculate when size > 3\n",
    "    if temp.shape[0]>3:\n",
    "        rate = (df.price - df.price.shift(1))/df.price.shift(1)\n",
    "        c = np.cov(rate[1:-1],rate[2:])[0,1]  # bec 0 is na\n",
    "        if c < 0:\n",
    "            return np.sqrt(-c)*2\n",
    "        else:\n",
    "            #positive_cov += 1\n",
    "            return -np.sqrt(c)*2\n",
    "    #low_trade_times += 1\n",
    "    return np.nan # 日内价格数量<3, 赋值np.nan, 以区分cov<0，事后可以方便改为0若有需要\n",
    "\n",
    "\n",
    "def roll_liquidity4(df):\n",
    "    # if cov> 0, treat positive covariances as if they are negative, resulting in a positive Roll spread estimate\n",
    "    #global low_trade_times, positive_cov\n",
    "    # first： 时间相同，取价格mean\n",
    "    temp = pd.DataFrame(df.groupby('hour_adj')['price'].mean()).sort_index(ascending=True)\n",
    "    # only calculate when size > 3\n",
    "    if temp.shape[0]>3:\n",
    "        rate = (df.price - df.price.shift(1))/df.price.shift(1)\n",
    "        c = np.cov(rate[1:-1],rate[2:])[0,1]  # bec 0 is na\n",
    "        if c < 0:\n",
    "            return np.sqrt(-c)*2\n",
    "        else:\n",
    "            #positive_cov += 1\n",
    "            return np.sqrt(c)*2\n",
    "    #low_trade_times += 1\n",
    "    return np.nan # 日内价格数量<3, 赋值np.nan, 以区分cov<0，事后可以方便改为0若有需要\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "low_trade_times = 0\n",
    "positive_cov = 0\n",
    "roll_daily = pd.DataFrame(data_use2.groupby(['id','date']).apply(roll_liquidity1),columns = ['roll_liquidity_method1'])\n",
    "low_trade_times, positive_cov"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "roll_daily2 = pd.DataFrame(data_use2.groupby(['id','date']).apply(roll_liquidity2),columns = ['roll_liquidity_method2'])\n",
    "roll_daily3 = pd.DataFrame(data_use2.groupby(['id','date']).apply(roll_liquidity3),columns = ['roll_liquidity_method3'])\n",
    "roll_daily4 = pd.DataFrame(data_use2.groupby(['id','date']).apply(roll_liquidity4),columns = ['roll_liquidity_method4'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "result = pd.concat([roll_daily,roll_daily2,roll_daily3,roll_daily4],axis = 1)\n",
    "result.reset_index(inplace=True)\n",
    "result.rename(columns={'id':'cusip_id'},inplace = True)\n",
    "result.to_csv('roll_daily_liquidity_measure_all.csv', index = False)"
   ]
  }
 ],
 "metadata": {
  "language_info": {
   "name": "python"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
